package com.sinosoft.app.packmarket.dao.imp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.sinosoft.app.packmarket.dao.PackEquityDao;
import com.sinosoft.app.packmarket.entity.PackEquity;

   /**
    * @ClassName PackEquityDaoJdbc实现类
    * @date 2017-2-16 
    * @author shangxp
    */  
@Repository
public class PackEquityDaoJdbc implements PackEquityDao {
	private static Logger log = Logger.getLogger(PackEquityDaoJdbc.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public int insertPackEquity(PackEquity packEquity) {
		String sqlStr = "insert into pack_equity ( "
							+" id ,"	
							+" equity ,"	
							+" analysis ,"	
							+" recommend ,"	
							+" tradingday ,"	
							+" increase ,"	
							+" compensate ,"	
							+" closingday ,"	
							+" price ,"	
							+" amount ,"	
							+" soldnum ,"	
							+" releasetime ,"	
							+" sys_user_id ,"	
							+" releaser ,"	
							+"is_del) values( "
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" 0 )";
		log.debug(sqlStr+"\n"+packEquity.toString());
		try{
			Object[] args = {
					packEquity.getId(),
					packEquity.getEquity(),
					packEquity.getAnalysis(),
					packEquity.getRecommend(),
					packEquity.getTradingday(),
					packEquity.getIncrease(),
					packEquity.getCompensate(),
					packEquity.getClosingday(),
					packEquity.getPrice(),
					packEquity.getAmount(),
					packEquity.getSoldnum(),
					packEquity.getReleasetime(),
					packEquity.getSysUserId(),
					packEquity.getReleaser()
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据添加异常 SQL："+sqlStr+"\n"+packEquity.toString()+e.getMessage());
		}
		return 0;
	}

	@Override
	public int updatePackEquity(PackEquity packEquity) {
		String sqlStr = "update pack_equity set "
									+" id = ? , "
									+" equity = ? , "
									+" analysis = ? , "
									+" recommend = ? , "
									+" tradingday = ? , "
									+" increase = ? , "
									+" compensate = ? , "
									+" closingday = ? , "
									+" price = ? , "
									+" amount = ? , "
									+" soldnum = ? , "
									+" releasetime = ? , "
									+" sys_user_id = ? , "
									+" releaser = ?  "
									+" where id = ?";
		log.debug(sqlStr+"\n"+packEquity.toString());
		try{
			Object[] args = {
					packEquity.getId(),
					packEquity.getEquity(),
					packEquity.getAnalysis(),
					packEquity.getRecommend(),
					packEquity.getTradingday(),
					packEquity.getIncrease(),
					packEquity.getCompensate(),
					packEquity.getClosingday(),
					packEquity.getPrice(),
					packEquity.getAmount(),
					packEquity.getSoldnum(),
					packEquity.getReleasetime(),
					packEquity.getSysUserId(),
					packEquity.getReleaser(),
					packEquity.getId()
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据更新异常 SQL："+sqlStr+"\n"+packEquity.toString()+e.getMessage());
		}
		return 0;
	}

	@Override
	public int deletePackEquity(int id) {
		String sqlStr = "update pack_equity set is_del = 1  where id = ?";
		try{
			Object[] args = {
					id
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据删除异常"+sqlStr+"\nID="+id+"\n"+e.getMessage());
		}	return 0;
	}

	@Override
	public PackEquity selectPackEquity(int id) {
		String sqlStr = "select * from pack_equity where is_del =0 and id = ?";
		log.debug(sqlStr+"\nID="+id);
		try{
			Object[] args = {
				id
			};
			return jdbcTemplate.queryForObject(sqlStr, args, new PackEquityMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\nID="+id+"\n"+e.getMessage());
		}	return null;
	}

	@Override
	public PackEquity selectPackEquity(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select * from pack_equity where is_del =0 " );
		Object[] args = null;
		if(queryKeys!=null && !queryKeys.isEmpty()){	
			args = new Object[queryKeys.size()];
			for(int i=0;i<queryKeys.size();i++){
				sqlStr.append(" and "+queryKeys.get(i)+"=? ");
				args[i] = queryValues.get(i);
			}
		}
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.queryForObject(sqlStr.toString(), args, new PackEquityMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public int selectCountPackEquity() {
		String sqlStr = "select count(1) from pack_equity where is_del =0 ";
		log.debug(sqlStr);
		try{
			return jdbcTemplate.queryForObject(sqlStr, Integer.class);
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return 0;
	}

	@Override
	public List<PackEquity> selectLimitPackEquity(int start, int size) {
		String sqlStr = "select ";
					sqlStr+=" id ,";
					sqlStr+=" equity ,";
					sqlStr+=" analysis ,";
					sqlStr+=" recommend ,";
					sqlStr+=" tradingday ,";
					sqlStr+=" increase ,";
					sqlStr+=" compensate ,";
					sqlStr+=" closingday ,";
					sqlStr+=" price ,";
					sqlStr+=" amount ,";
					sqlStr+=" soldnum ,";
					sqlStr+=" releasetime ,";
					sqlStr+=" sys_user_id ,";
					sqlStr+=" releaser ";
			sqlStr+=" from pack_equity where is_del =0  order by id desc limit ?,?";
		log.debug(sqlStr);
		try{
		Object[] args = {
				start,
				size
		};
			return jdbcTemplate.query(sqlStr, args, new PackEquityMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public List<PackEquity> selectAllPackEquity() {
		String sqlStr = "select ";
					sqlStr+=" id ,";
					sqlStr+=" equity ,";
					sqlStr+=" analysis ,";
					sqlStr+=" recommend ,";
					sqlStr+=" tradingday ,";
					sqlStr+=" increase ,";
					sqlStr+=" compensate ,";
					sqlStr+=" closingday ,";
					sqlStr+=" price ,";
					sqlStr+=" amount ,";
					sqlStr+=" soldnum ,";
					sqlStr+=" releasetime ,";
					sqlStr+=" sys_user_id ,";
					sqlStr+=" releaser ";
		 sqlStr+=" from pack_equity where is_del =0 order by id desc ";
		log.debug(sqlStr);
		try{
			return jdbcTemplate.query(sqlStr, new PackEquityMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public int selectCountPackEquity(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select count(1) from pack_equity where is_del = 0 ");
		Object[] args = null;
		if(queryKeys!=null && !queryKeys.isEmpty()){	
			args = new Object[queryKeys.size()];
			for(int i=0;i<queryKeys.size();i++){
				sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
				args[i] = queryValues.get(i) ;
			}
		}		
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.queryForObject(sqlStr.toString(), args,Integer.class);
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return 0;
	}

	@Override
	public List<PackEquity> selectLimitPackEquity(List<String> queryKeys,List<Object> queryValues,int start,int size) {
		StringBuffer sqlStr = new StringBuffer("select ");
					sqlStr.append(" id ,");
					sqlStr.append(" equity ,");
					sqlStr.append(" analysis ,");
					sqlStr.append(" recommend ,");
					sqlStr.append(" tradingday ,");
					sqlStr.append(" increase ,");
					sqlStr.append(" compensate ,");
					sqlStr.append(" closingday ,");
					sqlStr.append(" price ,");
					sqlStr.append(" amount ,");
					sqlStr.append(" soldnum ,");
					sqlStr.append(" releasetime ,");
					sqlStr.append(" sys_user_id ,");
					sqlStr.append(" releaser ");
			sqlStr.append(" from pack_equity where is_del =0 " );
		Object[] args = null;
			if(queryKeys!=null && !queryKeys.isEmpty()){	
				args = new Object[queryKeys.size()+2];
				for(int i=0;i<queryKeys.size();i++){
					sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
					args[i] =  queryValues.get(i) ;
				}
			}
		if(args==null){
			args = new Object[]{start,size};
		}else{
			args[queryKeys.size()] = start;
			args[queryKeys.size()+1] = size;
		}
		sqlStr.append(" order by id desc ");
		sqlStr.append(" limit ?,?");
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.query(sqlStr.toString(), args, new PackEquityMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public List<PackEquity> selectAllPackEquity(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select ");
					sqlStr.append(" id ,");
					sqlStr.append(" equity ,");
					sqlStr.append(" analysis ,");
					sqlStr.append(" recommend ,");
					sqlStr.append(" tradingday ,");
					sqlStr.append(" increase ,");
					sqlStr.append(" compensate ,");
					sqlStr.append(" closingday ,");
					sqlStr.append(" price ,");
					sqlStr.append(" amount ,");
					sqlStr.append(" soldnum ,");
					sqlStr.append(" releasetime ,");
					sqlStr.append(" sys_user_id ,");
					sqlStr.append(" releaser ");
			sqlStr.append("from pack_equity where is_del =0 " );
		Object[] args = null;
			if(queryKeys!=null && !queryKeys.isEmpty()){	
				args = new Object[queryKeys.size()];
				for(int i=0;i<queryKeys.size();i++){
					sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
					args[i] = queryValues.get(i);
				}
			}
		sqlStr.append(" order by id desc ");
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.query(sqlStr.toString(), args, new PackEquityMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	protected class PackEquityMapper implements RowMapper<PackEquity> {
		@Override
		public PackEquity mapRow(ResultSet arg0, int arg1) throws SQLException {
			PackEquity packEquity = new PackEquity();
			if(arg0!=null){
					packEquity.setId(arg0.getInt("id"));
					packEquity.setEquity(arg0.getString("equity"));
					packEquity.setAnalysis(arg0.getString("analysis"));
					packEquity.setRecommend(arg0.getString("recommend"));
					packEquity.setTradingday(arg0.getString("tradingday"));
					packEquity.setIncrease(arg0.getString("increase"));
					packEquity.setCompensate(arg0.getString("compensate"));
					packEquity.setClosingday(arg0.getString("closingday"));
					packEquity.setPrice(arg0.getString("price"));
					packEquity.setAmount(arg0.getString("amount"));
					packEquity.setSoldnum(arg0.getString("soldnum"));
					packEquity.setReleasetime(arg0.getString("releasetime"));
					packEquity.setSysUserId(arg0.getInt("sys_user_id"));
					packEquity.setReleaser(arg0.getString("releaser"));
				}
			return packEquity;
		}  
	}
}

